﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace UIT_CV_Management
{
    /// Class Definition
    /// Author: Khoi, Doan Quang
    /// Description:    Store the query result includes DataTable (if query success)
    ///                 and the Return Code to show the status of the query (success / fail)
    ///                 and with the Error Code, so you can determine what error is happened,
    ///                 and then have a clue to fix it quickly
    /// 
    /// Edit: 
    /// 
    /// Last edit: 04/01/13 11:00PM
    /// 
    public sealed class QueryResult
    {

        public DataTable Table = new DataTable();
        public int ReturnCode = 200;
        public String Error = "";

        public QueryResult() {
			
        }
    }

    public sealed class Database
    {
        private static readonly Database instance = new Database();
        
        //Primary Connection to use since the database object is created
        private SqlConnection PrimaryConnection;

        //Exchange
        private static String[,] DBCode = new String[,]{{"MaBaoCaoCanBo","A"},
                                                {"MaCheDoHoc","B"},
                                                {"MaDanhGiaTrinhDo","C"},
                                                {"MaDaoTaoDaiHoc","D"},
                                                {"MaDaoTaoHocHam","E"},
                                                {"MaDaoTaoNgoaiNgu","F"},
                                                {"MaDaoTaoTrenDaiHoc","G"},
                                                {"MaDiaDiem","H"},
                                                {"MaHoatDongKHKT","I"},
                                                {"MaHoatDongKhoaHocKyThuat","J"},
                                                {"MaHoiNghiKHKT","K"},
                                                {"MaKhenThuong","L"},
                                                {"MaLyLichKhoaHoc","M"},
                                                {"MaLyLichSoLuoc","N"},
                                                {"MaMonThiTotNghiep","O"},
                                                {"MaNgheNghiep","P"},
                                                {"MaNghienCuu","Q"},
                                                {"MaNoiHoc","R"},
                                                {"MaQuaTrinhDaoTao","S"},
                                                {"MaQuaTrinhHDKHKT","T"},
                                                {"MaTenHocHam","U"},
                                                {"MaThongTinPhu","V"},
                                                {"MaThucTap","W"},
                                                {"MaTieuLuan","X"},
                                                {"MaTotNghiep","Y"},
                                                {"ID","Z"}};
        //Share instance

        public static Database shareDatabase
        {
            get
            {
                //return current instance while accessing shareDatabase
                return instance;
            }
        }

        /// <summary>
        /// Create custom connection string and open the connection
        /// </summary>
        /// <param name="Host">Host name of the server containing database</param>
        /// <param name="Username">Username provided to access the database (with neccessary permissions)</param>
        /// <param name="Password">Password provided</param>
        /// <param name="Database">Database name in the database server</param>
        private Database(string Host, string Username, string Password, string Database)
        {
            //Create Connection String
            String ConnectionString = "User Id=" + Username + ";Password=" + Password +
                ";Server=" + Host + ";Database=" + Database;
            //Init a connection
            PrimaryConnection = new SqlConnection(ConnectionString);
            //Open the connection
            PrimaryConnection.Open();

        }

        /// <summary>
        /// Constructor create the connection and open it (with default Connection String)
        /// </summary>
        private Database()
        {
            //Create default Connection String
            String ConnectionString = "Data Source=KHOIDOAN-PC\\SQLEXPRESS;Initial Catalog=uit_cv;"+
                "Persist Security Info=True;User ID=sa;Password=admin10520462";
            //Init a connection
            PrimaryConnection = new SqlConnection(ConnectionString);
            //Open the connection
            PrimaryConnection.Open();
        }

        /// <summary>
        /// Receice the SQL string and do the query
        /// </summary>
        /// <param name="SQL">Query String</param>
        /// <returns>QueryResult (include Error Code & Error Message)</returns>
        public QueryResult Query(string SQL)
        {
            //QueryResult object to store the result of the SQL
            QueryResult result = new QueryResult();
            
            //First check if they have query input
            if (SQL != "")
            {
                //Open the connection if it was closed for any reason
                if (PrimaryConnection.State == System.Data.ConnectionState.Closed)
                    PrimaryConnection.Open();

                //SELECT is a retrieve-SQL and then put the data into DataTable
                if (SQL.Substring(0, 6) == "SELECT")
                {
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(SQL, PrimaryConnection);
                    try
                    {
                        dataAdapter.Fill(result.Table);
                        //Fill data to Table and then return OK code (200)
                        if (result.Table.Rows.Count > 0)
                        {
                            result.ReturnCode = 200;
                        }
                        else
                        {
                            result.ReturnCode = 404;
                            result.Error = Language._("GLOBAL_NO_RECORD");
                        }
                        
                    }
                    catch (Exception err)
                    {
                        //There's some mistakes with the query or the database or the data exist in the database
                        //then return the Conflict code with error message from the catched Exception
                        result.Table = new DataTable();
                        result.Error = err.Message;
                        result.ReturnCode = 409;
                    }
                    
                }
                else
                {
                    SqlCommand SQLC = new SqlCommand(SQL, PrimaryConnection);
                    try
                    {
                        //Execute the query (non-data) and then return OK code
                        SQLC.ExecuteNonQuery();
                        result.ReturnCode = 200;
                    }
                    catch (Exception err)
                    {
                        //Return conflict code
                        result.Error = err.Message;
                        result.ReturnCode = 409;
                    }
                    SQLC.Connection.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// Check the duplicate value of the table
        /// </summary>
        /// <param name="Table">The Table you want to check</param>
        /// <param name="Column">The Column you want to check</param>
        /// <param name="Value">The Value you want to check</param>
        /// <returns></returns>
        public static Boolean isDupplicate(String Table, String Column, String Value)
        {
            QueryResult result = new QueryResult();
            try
            {
                //Check the duplicate value (OK)
                result = instance.Query("SELECT * FROM " + Table + " WHERE '" +
                    Column + "' = '" + Value + "'");
                result.ReturnCode = 200;
                if (result.Table.Rows.Count > 0)
                    return true;
            }
            catch (Exception err)
            {
                //There's some mistakes with the user input value or database
                result.ReturnCode = 409;
                result.Error = err.Message;
            }
            return false;
        }


        /// <summary>
        /// Get the ID in order to insert a new row
        /// </summary>
        /// <param name="Table">Table name</param>
        /// <param name="Column">Column name (contains ID)</param>
        /// <returns></returns>
        public static String getID(String Table, String Column)
        {
            QueryResult result = new QueryResult();
            try
            {
                //Check the duplicate value (OK)
                result = instance.Query("SELECT Max(" + Column + ") FROM " + Table);
                result.ReturnCode = 200;
                if (result.Table.Rows.Count > 0 && result.Table.Rows[0][0].ToString() != "") {
                    String Number = result.Table.Rows[0][0].ToString().Substring(1);
                    return formatID(Column, Convert.ToInt32(Number) + 1);
                }
                else
                    return formatID(Column, 0);
            }
            catch (Exception err)
            {
                //There's some mistakes with the user input value or database
                result.ReturnCode = 409;
                result.Error = err.Message;
            }
            return null;
        }

        /// <summary>
        /// Format number to string
        /// </summary>
        /// <param name="Column">Column name</param>
        /// <param name="Value">Value of number</param>
        /// <returns></returns>
        private static String formatID(String Column, Int32 Value)
        {
            for (int i = 0; i < DBCode.Length / 2; i++)
                if (DBCode[i, 0] == Column)
                    return DBCode[i,1] + Value.ToString("000000000");
            return null;
        }

        /// <summary>
        /// Convert DateTime to SQL format
        /// </summary>
        /// <param name="_DateTime">DateTime input</param>
        /// <returns></returns>
        public static String sqlDateTime(DateTime _DateTime) {
            return _DateTime.ToString("yyyy-MM-dd HH:mm:ss");
        }
    }
}